package persistence.ingredient.ingredientDAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import business.article.entities.Ingredient;

import article.exceptions.badClassException;
import persistence.DAO.DAOHelper;

public class IngredientDAO {


	/**
	 * Singleton implementation begin
	 */
	private static IngredientDAO instance = null;

	private IngredientDAO() {
	}

	public static IngredientDAO getInstance() {
		if (instance == null)
			instance = new IngredientDAO();
		return instance;
	}

	/**
	 * Singleton implementation end
	 * 
	 * @throws badClassException
	 */
	

	public void addOutOfStock(ArrayList<Ingredient> ingredientes){
		
		for (Ingredient i:ingredientes){
			String sql = "UPDATE Ingredient SET ";
			sql = sql + " in_stock='0'";
			sql = sql + " WHERE inner_key="
					+ String.valueOf(i.getId());
			Connection con=null;
			try {
				con=DAOHelper.getInstance().getConnection();
				con.createStatement().executeUpdate(sql);

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				if(con!=null)
					try {
						con.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}	
		  }

		}
		
	}
	
	public void addInStock(ArrayList<Ingredient> ingredientes){
		for (Ingredient i:ingredientes){
			String sql = "UPDATE Ingredient SET ";
			sql = sql + "in_stock='1'";
			sql = sql + " WHERE inner_key="
					+ String.valueOf(i.getId());
			Connection con=null;
			try {
				con=DAOHelper.getInstance().getConnection();
				Statement st=con.createStatement();
				st.executeUpdate(sql);

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				if(con!=null)
					try {
						con.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}	
		  }
			}	
	}
	
	public Ingredient[] getAllIngredients() {
		Connection con=null;
		  try {
			  con=DAOHelper.getInstance().getConnection();
			  Statement st=con.createStatement();
			  ResultSet r=st.executeQuery(
		  "SELECT name,in_stock,inner_key FROM Ingredient");
		  ArrayList<Ingredient> ingredientes=new ArrayList<Ingredient>();
		  
		  while(r.next()){ingredientes.add(new Ingredient(r.getString(1), r.getBoolean(2), r.getInt(3))); }
		  
		  Ingredient[] ingr=new Ingredient[ingredientes.size()];
		  ingredientes.toArray(ingr); return ingr; } catch (SQLException e) { e.printStackTrace();
		  }finally{
				if(con!=null)
					try {
						con.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}	
		  }
		  return null;
	}

	
	public Ingredient[] getInStockIngredients() {
		Connection con=null;
		  try { 
			  con=DAOHelper.getInstance().getConnection();
			  Statement st=con.createStatement();
			  ResultSet r=st.executeQuery(
		  "SELECT name,in_stock,inner_key FROM Ingredient WHERE in_stock=1");
		  ArrayList<Ingredient> ingredientes=new ArrayList<Ingredient>();
		  
		  while(r.next()){ingredientes.add(new Ingredient(r.getString(1), r.getBoolean(2), r.getInt(3))); }
		  
		  Ingredient[] ingr=new Ingredient[ingredientes.size()];
		  ingredientes.toArray(ingr); return ingr; } catch (SQLException e) { e.printStackTrace();
		  }finally{
				if(con!=null)
					try {
						con.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}	
		  }
		  return null;
	}

	
	public Ingredient[] getOutStockIngredients() {
		Connection con=null;
		try { 
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet r=st.executeQuery(
				  "SELECT name,in_stock,inner_key FROM Ingredient WHERE in_stock=0");
				  ArrayList<Ingredient> ingredientes=new ArrayList<Ingredient>();
				  
				  while(r.next()){ingredientes.add(new Ingredient(r.getString(1), r.getBoolean(2), r.getInt(3))); }
				  Ingredient[] ingr=new Ingredient[ingredientes.size()];
				  ingredientes.toArray(ingr); return ingr; } catch (SQLException e) { e.printStackTrace();
				  }finally{
						if(con!=null)
							try {
								con.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}	
				  }
				  return null;
	}

	
	public void newIngredient(String name) {
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			st.execute("INSERT INTO Ingredient (name,in_stock) VALUES('"
					+ name
					+ "',"
					+ "1"+ ")");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
	}

}
